﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Windows.Forms;

namespace SMS.dao
{
    public class BlockDao
    {
        private static readonly BlockDao INSTANCE = new BlockDao();

        public static BlockDao Instance
        {
            get
            {
                return INSTANCE;
            }
        }

        private BlockDao() { }
		//===============quang
        public int insert(String blockName)
        {
            String sql = "insert into block (blo_name) values (@blockName)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blockName", blockName));
                   

            return DataFetcher.Instance.execute();
        }

        public DataTable findAll()
        {
            String sql = "select * " +
                         "from block ";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

		public int update(String blockName, String blockID)
        {
            String sql = "update block set blo_name = @blockName where blo_id = @blockID";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blockName", blockName));
            DataFetcher.Instance.addParameter(new SqlParameter("@blockID", Int32.Parse(blockID)));
            return DataFetcher.Instance.execute();
        }

        public DataTable countClazzByBlockId()
        {
            String sql = "select b1.BLO_ID, b1.BLO_NAME, isnull(a.count, 0) as BLO_COUNTED " +
                        "from Block b1 left join " +
                        "(select b2.BLO_ID, b2.BLO_NAME, count(c.CLA_ID) as count " +
                        "from BLOCK b2, CLAZZ c where b2.BLO_ID = c.BLO_ID " +
                        "group by b2.BLO_ID, b2.BLO_NAME) as a on b1.BLO_ID = a.BLO_ID " + 
                        "order by b1.BLO_NAME ";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public ArrayList findById(int blockID)
        {
            String sql = "select * from block where blo_id = @id";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@id", blockID));
            return DataFetcher.Instance.getOneRow();
        }

        public DataTable countClazzInBlock(int blockID)
        {
            string sql = "select * from clazz where blo_id = @blockID";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blockID",blockID));
            return DataFetcher.Instance.fetch();
        }

        public int deleteBlockInNull( int blockID) 
        {
            string sql = "delete from block where blo_id = @blo_id";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blo_id", blockID));
            return DataFetcher.Instance.execute();
        }

        public DataTable findBlockName(string blockName)
        {
            string sql = "select blo_name from block where blo_name = @blo_name";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@blo_name", blockName));
            return DataFetcher.Instance.fetch();
        }
        //==================================
    }
}
